#!/usr/bin/env python
# -*- coding: utf-8 -*-


import sqlite3

def getYears():
    '''
    select all years from db
    '''
    connection = sqlite3.connect("teachers.db")
    c = connection.cursor()
    c.execute('''SELECT ano from teachers''')
    connection.commit()
    years = {}
    for year in c.fetchall():
        years[year[0]] = {}
        pass
        
    pass
    connection.close()
    return years
pass

def getEstablishments(year):
    '''
    select all establishments from one year from db
    '''
    connection = sqlite3.connect("teachers.db")
    c = connection.cursor()
    c.execute('''SELECT nome_estabelecimento from teachers WHERE ano = "{0}"'''.\
              format(year));
    connection.commit()
    establishments = {}
    for establishment in c.fetchall():
        establishments[establishment[0]] = {}
        pass
    pass
    connection.close()
    return establishments
pass

def getCategories(year, establishment):
    '''
    select all establishments from one year from db
    '''
    #establishment = establishment.encode("utf-8")
    
    #establishment = establishment.replace("\\", r'\\\\');
    establishment = establishment.replace("\"", r'\\x22')
    connection = sqlite3.connect("teachers.db")
    c = connection.cursor()
    query = ""
    c.execute('''SELECT categoria from teachers WHERE ano = "{0}"
              AND nome_estabelecimento = "{1}"'''.\
              format(year, establishment.encode("utf-8")))
    connection.commit()
    categories = {}
    for category in c.fetchall():
        categories[category[0]] = {}
        pass
    pass
    connection.close()
    return categories
pass

def getTeachersByField(year, establishment, field, value):
    establishment = establishment.replace("\"", r'\\x22')
    value = value.replace("\"", r'\\x22')
    connection = sqlite3.connect("teachers.db")
    c = connection.cursor()
    query = ""
    c.execute('''SELECT id from teachers WHERE ano = "{0}"
              AND nome_estabelecimento = "{1}" AND {2} = "{3}"'''.\
              format(year, establishment.encode("utf-8"), field, value.encode("utf-8")))
    connection.commit()
    teachers = []
    for teacher in c.fetchall():
        teachers.append(teacher)
    pass
    return teachers 
pass

def getTeachersByEst(year, establishment):
    establishment = establishment.replace("\"", r'\\x22')
    connection = sqlite3.connect("teachers.db")
    c = connection.cursor()
    query = ""
    c.execute('''SELECT id from teachers WHERE ano = "{0}"
              AND nome_estabelecimento = "{1}"'''.\
              format(year, establishment.encode("utf-8")))
    connection.commit()
    teachers = []
    for teacher in c.fetchall():
        teachers.append(teacher)
    pass
    return teachers 
pass

tudo = getYears()
for anos in tudo.keys():
    tudo[anos] = getEstablishments(anos)
    for estabelecimentos in tudo[anos].keys():
        c = getTeachersByEst(anos, estabelecimentos)
        print estabelecimentos + " (" + str(len(c)) + ")"


    
